DECLARE @BillOfMaterialsHeaderId INT
DECLARE @ModelPublicationId INT
DECLARE @ServicePartsPageId INT
DECLARE @LanguageCode NVARCHAR(MAX)
DECLARE @ModelId INT

SELECT  BillOfMaterialsLineId,
        PartNumber
FROM    BillOfMaterialsLine
WHERE   BillOfMaterialsHeaderId = @BillOfMaterialsHeaderId
        AND ( ( BillOfMaterialsLine.MainLinkExcludedFrom = 0
                AND EXISTS ( SELECT 1
                             FROM   MainPartLink
                             WHERE  MainPartLink.PartNumber = BillOfMaterialsLine.PartNumber
                                    AND MainPartLink.ModelPublicationId = @ModelPublicationId
                                    AND MainPartLink.ServicePartsPageId <> @ServicePartsPageId )
              )
              OR ( BillOfMaterialsLine.InnerLinkExcludedFrom = 0
                   AND EXISTS ( SELECT  1
                                FROM    InnerPartLink
                                        INNER JOIN Model
                                            ON InnerPartLink.ModelCode = Model.ModelCode
                                               AND InnerPartLink.ModelSequence = Model.ModelSequence
                                        INNER JOIN Publication
                                            ON InnerPartLink.PublicationId = Publication.PublicationId
                                        INNER JOIN LocalizedPublication
                                            ON InnerPartLink.PublicationId = LocalizedPublication.PublicationId
                                               AND LocalizedPublication.LanguageCode = @LanguageCode
                                WHERE   InnerPartLink.PartNumber = BillOfMaterialsLine.PartNumber
                                        AND Model.ModelId = @ModelId
                                        AND Publication.FollowingPublicationId IS NULL )
                 )
            )